from typing import NoReturn

from sqlalchemy import and_, func, or_, select
from sqlalchemy.exc import DBAPIError, IntegrityError
from sqlalchemy.orm import joinedload

from src.application.common.exceptions import RepoError
from src.application.proxy.dto.proxies import ProxyDTOs
from src.application.proxy.dto.proxy import ProxyDTO
from src.application.proxy.enums import ProxyStatus
from src.application.proxy.interfaces.reader.proxy_reader import GetProxiesFilters, ProxyReader
from src.application.proxy.interfaces.repo.proxy_repo import ProxyRepo
from src.application.server.enums import ServerPrivacy
from src.application.user.exceptions import UserEmailAlreadyExist
from src.domain.order.entities.proxy import ProxyEntity
from src.domain.server.entities.category import CategoryEntity
from src.domain.user.aggregates import UserAggregate
from src.infrastructure.db.converters.category import convert_category_entity_to_db_model
from src.infrastructure.db.converters.order import convert_db_model_to_order_entity
from src.infrastructure.db.converters.proxy import (
    convert_db_model_to_proxy_dto,
    convert_db_model_to_proxy_entity,
    convert_proxy_entity_to_db_model,
)
from src.infrastructure.db.converters.user import convert_user_entity_to_db_model
from src.infrastructure.db.exception_mapper import exception_mapper
from src.infrastructure.db.models import Order, Proxy, Server
from src.infrastructure.db.models.m2ms import orders_proxies, proxies_users
from src.infrastructure.db.repositories.base import SQLAlchemyRepo


class ProxyReaderImpl(SQLAlchemyRepo, ProxyReader):
    @exception_mapper
    async def get_proxy_by_id(self, proxy_id: int) -> ProxyDTO:
        proxy: Proxy | None = await self._session.scalar(
            select(Proxy).where(
                Proxy.id == proxy_id,
            )
        )

        return convert_db_model_to_proxy_dto(proxy)

    @exception_mapper
    async def get_available_proxies(
        self,
        user: UserAggregate,
        category: CategoryEntity,
        filters: GetProxiesFilters,
    ) -> list[ProxyDTOs]:
        db_user = convert_user_entity_to_db_model(user)
        db_category = convert_category_entity_to_db_model(category)

        q = (
            select(Proxy)
            .outerjoin(Proxy.server)
            .outerjoin(Proxy.users)
            .outerjoin(Proxy.orders)
            .filter(Proxy.status != ProxyStatus.invalid)
            .filter(Server.proxy_type == filters.proxy_type)
            .filter(Server.proxy_privacy == filters.proxy_privacy)
            .filter(Server.country_id == filters.country_id)
            .filter(Server.provider_id == filters.provider_id)
        )

        if filters.proxy_privacy == ServerPrivacy.private:
            q = q.filter(and_(~Proxy.users.any(), ~Proxy.orders.any()))

        elif filters.proxy_privacy == ServerPrivacy.shared:
            q = (
                q.filter(Server.categories.contains(db_category))
                .filter(~Proxy.users.contains(db_user))
                .filter(
                    or_(
                        Order.site_id != filters.site_id,
                        ~Proxy.orders.any(),
                    )
                )
                .group_by(Proxy.id)
                .having(
                    and_(
                        select(func.count())
                        .select_from(orders_proxies)
                        .where(orders_proxies.c.proxy_id == Proxy.id)
                        .as_scalar()
                        < 3,
                        select(func.count())
                        .select_from(proxies_users)
                        .where(proxies_users.c.proxy_id == Proxy.id)
                        .as_scalar()
                        < 3,
                    )
                )
            ).options(joinedload(Proxy.users), joinedload(Proxy.orders))

        if filters.limit:
            q = q.limit(filters.limit)

        result = await self._session.scalars(q)

        available_proxies = result.unique().all()

        return available_proxies

    @exception_mapper
    async def get_all_proxies(self) -> list[ProxyDTOs]:
        query = select(Proxy)

        result = await self._session.scalars(query)
        proxies: list[Proxy] = list(result)

        return [convert_db_model_to_proxy_dto(proxy) for proxy in proxies]


class ProxyRepoImpl(SQLAlchemyRepo, ProxyRepo):
    @exception_mapper
    async def acquire_proxy_by_id(self, proxy_id: int) -> ProxyEntity:
        proxy: Proxy | None = await self._session.scalar(
            select(Proxy)
            .where(
                Proxy.id == proxy_id,
            )
            .with_for_update()
        )

        return convert_db_model_to_proxy_entity(proxy)

    @exception_mapper
    async def update_proxy(self, proxy: ProxyEntity) -> None:
        db_proxy = convert_proxy_entity_to_db_model(proxy)
        try:
            await self._session.merge(db_proxy)
        except IntegrityError as err:
            self._parse_error(err, db_proxy)

    def _parse_error(self, err: DBAPIError, user: UserAggregate) -> NoReturn:
        match err.__cause__.__cause__.constraint_name:  # type: ignore
            # case "pk_users":
            #     raise UserIdAlreadyExists(user.id.to_uuid()) from err
            case "uq_user_email":
                raise UserEmailAlreadyExist(str(user.email)) from err
            case _:
                raise RepoError from err
